02. Optimizing Query and Controlling Costs

BigQuery를 사용하다보면 쿼리를 발로 짜도 척!하니 잘 알아듣고 상상 이상의 속도로 데이터를 찾아줍니다
수만개 이상의 CPU와 수천개의 디스크를 사용하기 때문에 저희는 컴퓨팅을 걱정할 필요는 없지만, 이왕이면 조금 더 효율적으로 쿼리를 짜면 어떨까- 싶어서 구글 Document를 읽으며 인상깊은 것들을 정리했습니다
오라클 성능 고도화 원리와 해법2 같은 책을 이미 보신 분들은 당연한 소리라고 생각할 수 있지만, 해당 책을 보지 않은 분들을 위한 문서입니다
단, 아래의 가이드라인을 모두 지키면 할 수 있는 것들이 많이 없습니다. 참고사항 정도로 생각해주시면 될 것 같습니다!

Factors of Optimizing

Input data and Data source (I/O) : 쿼리에서 읽는 바이트 수는 얼마인가요?
Communication between nodes (shuffling) : 쿼리가 다음 단계로 전달되는 바이트 수는 몇 바이트며, 각 슬롯에 몇 바이트의 쿼리가 전달되나요?
Computation : 쿼리에 어느 정도의 CPU 작업이 필요한가요?
Outputs (materialization) : 쿼리에서 몇 바이트를 사용할 수 있나요?
Query anti-patterns : SQL 표준 형식을 따르는 쿼리인가요?

SELECT * 피하기
- 대부분의 DB에서 SELECT *은 항상 비싼 질의라서 피하길 권하고 있습니다. 데이터의 모양이 궁금하시면 Table의 preview를 이용해주세요!
- SELECT * LIMIT 10 이런 쿼리를 진행해도 영향이 없습니다. BigQuery는 컬럼 기반으로 데이터를 읽기 때문에 10개로 제한해도 모두 읽은 후 10개의 결과만 출력할 뿐입니다
Where문에 date 지정하기
- Table에 Date로 Partition되어 있다면, Date 해당 기준만 Where 조건에 걸면 Input data의 양을 줄일 수 있습니다
```
WHERE _PARTITIONTIME
BETWEEN TIMESTAMP('20171001')
AND TIMESTAMP('20171004)
```
Denormalize data ( 비정규화 )
- BigQuery는 데이터가 비정규화 ( Nested and Repeated field를 가진 상태)일 경우 성능이 더욱 좋습니다. 가능하면 비정규화된 상태로 데이터를 유지시켜주세요.
Nested data ( STRUCT )
- 데이터를 중첩(Nest)하면 inline으로 데이터를 저장할 수 있습니다.
- LegacySQL Nested data = StandardSQL의 STRUCT
Repeated data ( ARRAY )
- Repeated data는 1대다 관계를 inline으로 데이터를 저장할 수 있습니다.
- Repeated data가 존재하면 Shuffling이 필요하지 않습니다
- LegasySQL Repeated data = StandardSQL의 ARRAY

Query날린 결과를 Temp Table로 저장하기
- Query날린 결과는 데이터의 바이트수가 (보통) 작아집니다. 이 지속적으로 참고해야 한다면, Temp Table로 저장해서 해당 테이블에 쿼리를 날리면 좋습니다! (Cashe 기능이 존재하지만 24시간만 cashe됩니다

Reduce data before using a Join
- 당연한 말이지만, Join을 진행하기 전에 데이터를 가볍게 만든 후 Join을 하면 해당 연산량을 줄일 수 있습니다
Do not treat WITH clauses as prepared statements
- StandardSQL에서만 사용할 수 있는 WITH 구문은 쿼리가 실행될 때 생기는 추상적인 절입니다. 가독성을 위해 With문을 사용해주세요 (=반복되는 Table을 참고해야할 시 이용!)
Avoid date-named tables
- 날짜가 지정된 테이블 (예를 들면 firebase 연동시 생기는 테이블)보단 날짜 분할 테이블을 사용하는 것이 더 효율적이라고 합니다. 날짜가 지정된 테이블을 만들때마다 테이블의 스키마와 메타 데이터의 복사본을 유지해야 하고, 사용할 때 사용 권한을 확인해야 하기에 날짜 분할 테이블을 사용해주세요

Avoid repeatedly transforming data via SQL queries
- ETL 작업을 수행할 경우 반복적으로 변환하는 함수(예를 들어 정규식을 사용해 데이터를 추출하는 경우)는 추가적 계산이 필요하기 때문에, Destination Table에 쿼리를 날려 저장한 후, 사용하는 것을 추천합니다
Avoid JavaScript user-defined functions
- UDF 기능을 사용하면 Java 서브프로세스의 인스턴스 작성이 필요하기 때문에, 기본 내장 함수를 사용하길 권장합니다
Use approximate aggregation functions
- legacy SQL에서 정확한 값을 COUNT하고 싶다면 EXACT_COUNT_DISTINCT(id)를 사용해야 합니다. 이 함수는 속도가 조금 더 걸리기 때문에 빠르게 결과를 보고싶다면 모사 함수인 COUNT(DISTINCT id)를 사용하면 더 빠릅니다
- Standard SQL에서는 APPROX_COUNT_DISTINCT(id)가 모사 함수며, COUNT(DISTINCT id)가 정확한 값을 return합니다
- 개인적인 생각으론 COUNT할 경우는 컴퓨팅 리소스를 더 사용하더라도 정확한 값을 얻는 것을 추천드리고 싶습니다
Order query operations to maximize performance
- 가장 바깥쪽 쿼리 또는 분석 함수일 경우 ORDER BY를 사용하는 것을 권장합니다

Avoid repeated joins and subqueries
- 동일한 테이블을 반복적으로 Join해야하는 경우 스키마를 nested repeated data로 표현하는 것이 성능 향상에 도움이 될 것입니다
- 동일한 하위 쿼리를 반복하는 것은 성능에 영향을 주기 때문에, destination table에 쿼리의 결과를 저장한 후 사용하는 것을 추천합니다
Carefully consider materializing large result sets
- BigQuery는 캐시된 결과를 약 128MB로 압축합니다. 이보다 더 큰 결과를 반환하는 쿼리는 'Reseponse too large' 오류를 발생합니다
- legacySQL의 경우 each 키워드를 작성하면 오류를 해결할 수 있습니다 (group each by, join each)
Use a LIMIT clause with large sorts
- 많은 값을 정렬하는 경우엔 LIMIT 절을 사용하면 속도를 향상할 수 있습니다
- ORDER BY 절을 사용하면 'Resources exceeded errors'를 반환하는데, 이 경우 LIMIT을 사용하면 됩니다

Self-join
- Self join이 필요한 경우라면 대부분 window function을 사용하면 해결됩니다!
Cross joins (Cartesian product)
- Cross join은 첫번째 테이블이 각 행이 두번쨰 테이블의 모든 행에 조인되는 쿼리로 M x N만큼 Join을 실행합니다. 이 경우 Cross join 전에 집계함수를 사용해 데이터를 줄인 후, 사용하는 것이 효율적입니다
DML statements that update or insert single rows
- BigQuery의 DML은 대량 업데이트를 위한 것이기 때문에, 한번에 다량의 데이터를 update/insert 하도록 쿼리를 작성해주세요